package com.bandaotixi.cashier.api.core.mybatis;

import lombok.extern.slf4j.Slf4j;

import java.util.*;
import java.util.stream.Collectors;

@Slf4j
public class CommonQueryProvider {

    public String relationalQuery(Map<String, Object> params) {
        String table = (String) params.get("table");
        String keyColumn = (String) params.get("keyColumn");
        String valueColumn = (String) params.get("valueColumn");
        Map<String, Object> where = (Map<String, Object>) params.get("where");

        // 安全校验（白名单/正则），避免 SQL 注入
        if (!table.matches("^[a-zA-Z0-9_]+$")) {
            throw new IllegalArgumentException("非法表名：" + table);
        }

        for (String col : new String[]{keyColumn, valueColumn}) {
            if (!col.trim().matches("^[a-zA-Z0-9_.*]+$")) {
                throw new IllegalArgumentException("非法字段名：" + col);
            }
        }

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ")
                .append(keyColumn).append(" as `key`, ")
                .append(valueColumn).append(" as `value`")
                .append(" FROM ").append(table);

        sql.append(buildWhere(where));
        log.debug("SQL: {}", sql);
        return sql.toString();
    }

    public String buildUpsert(Map<String, Object> params) {
        String table = (String) params.get("table");
        Map<String, Object> data = (Map<String, Object>) params.get("data");

        // 校验表名
        if (!table.matches("^[a-zA-Z0-9_]+$")) {
            throw new IllegalArgumentException("非法表名: " + table);
        }

        StringJoiner columns = new StringJoiner(", ");
        StringJoiner values = new StringJoiner(", ");
        StringJoiner updates = new StringJoiner(", ");

        for (String key : data.keySet()) {
            if (!key.matches("^[a-zA-Z0-9_]+$")) {
                throw new IllegalArgumentException("非法字段名: " + key);
            }

            columns.add(key);
            values.add("#{data." + key + "}");
            updates.add(key + " = VALUES(" + key + ")");
        }

        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ").append(table).append(" (")
                .append(columns).append(") VALUES (")
                .append(values).append(") ")
                .append("ON DUPLICATE KEY UPDATE ")
                .append(updates);
        log.debug("SQL: {}", sql);
        return sql.toString();
    }

    public String buildBatchUpsert(Map<String, Object> params) {
        String table = (String) params.get("table");
        List<Map<String, Object>> dataList = (List<Map<String, Object>>) params.get("dataList");

        if (dataList == null || dataList.isEmpty()) {
            throw new IllegalArgumentException("数据不能为空");
        }

        Map<String, Object> firstRow = dataList.get(0);
        Set<String> columns = firstRow.keySet();

        // 校验表名和列名
        if (!table.matches("^[a-zA-Z0-9_]+$")) throw new IllegalArgumentException("非法表名: " + table);
        for (String col : columns) {
            if (!col.matches("^[a-zA-Z0-9_]+$")) throw new IllegalArgumentException("非法列名: " + col);
        }

        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ").append(table).append(" (")
                .append(String.join(", ", columns)).append(") VALUES ");

        for (int i = 0; i < dataList.size(); i++) {
            sql.append("(");
            int finalI = i;
            sql.append(columns.stream()
                    .map(col -> "#{dataList[" + finalI + "]." + col + "}")
                    .collect(Collectors.joining(", ")));
            sql.append(")");
            if (i < dataList.size() - 1) sql.append(", ");
        }

        // 构造 ON DUPLICATE KEY UPDATE 子句
        sql.append(" ON DUPLICATE KEY UPDATE ");
        sql.append(columns.stream()
                .map(col -> col + " = VALUES(" + col + ")")
                .collect(Collectors.joining(", ")));
        log.debug("SQL: {}", sql);
        return sql.toString();
    }

    public String buildDelete(Map<String, Object> params) {
        String table = (String) params.get("table");
        // 校验表名
        if (!table.matches("^[a-zA-Z0-9_]+$")) {
            throw new IllegalArgumentException("非法表名: " + table);
        }
        Map<String, Object> data = (Map<String, Object>) params.get("where");
        StringBuilder sql = new StringBuilder();
        sql.append("DELETE FROM ").append(table);
        sql.append(buildWhere(data));
        log.debug("SQL: {}", sql);
        return sql.toString();
    }

    private static String buildWhere(Map<String, Object> data) {
        if (data == null || data.isEmpty()) {
            return "";
        }

        StringJoiner where = new StringJoiner(" AND ");

        for (Map.Entry<String, Object> entry : data.entrySet()) {
            String key = entry.getKey();
            Object value = entry.getValue();

            boolean isIn = false;
            String field = key;

            // 检测是否为 in: 前缀
            if (key.startsWith("in:") || value instanceof Collection<?>) {
                isIn = true;
                field = key.substring(3);
            }

            if (!key.matches("^[a-zA-Z0-9_:]+$")) {
                throw new IllegalArgumentException("非法字段名: " + key);
            }

            if (isIn) {
                where.add(field + " IN " + "<foreach collection='where." + key + "' item='item' open='(' separator=',' close=')'>#{item}</foreach>");
            } else {
                where.add(field + " = #{where." + key + "}");
            }
        }

        return where.length() > 0 ? " WHERE " + where : "";
    }
}
